<?php
set_include_path($_SERVER['DOCUMENT_ROOT']);
include_once 'admin/includes/functions/admin-functions.php';
ini_set('display_errors',1);
ini_set('error_reporting',E_ALL ^ E_NOTICE ^ E_WARNING);
ini_set('memory_limit','500M');
global  $CONFIG,$SMARTY,$DBobject;

$GLOBALS['USRADDR'] = array();
//Briony Vince Franchisee ID (admin_id = 123)
$aid = 123;
echo "=> Cron started<br>";

$whereloc = '';
$params = array();
$attachmentFile = array(); 

//SELECT all locations managed by Briony Vince
$sql = "SELECT location_object_id FROM tbl_location 
				LEFT JOIN tbl_franchisee ON franchisee_object_id = location_franchisee_id 
				LEFT JOIN tbl_access ON access_franchisee_id = franchisee_object_id
		 		WHERE access_flag = 1 AND access_deleted IS NULL AND franchisee_deleted IS NULL AND franchisee_published = 1 AND location_deleted IS NULL AND location_published = 1 AND access_admin_id = :id";
if($res = $DBobject->wrappedSql($sql, array(":id"=>$aid))){
	$loc = array();
	foreach($res as $r){
		$loc[] = "class_location_id = :loc{$r['location_object_id']}";
		$params["loc{$r['location_object_id']}"] = $r['location_object_id'];
		//$tmp .= " OR  class_location_id = {$r['location_object_id']}"; 
	}
	$whereloc = ' AND ('. implode(' OR ', $loc). ') ';
}
if(empty($whereloc)) die("THIS FRANCHISEE DOESN'T HAVE ANY LOCATIONS");
	


//Select all locations which belong to this franchisee
$sql = "SELECT student_id, enrolment_week as trial_date, CONCAT(student_name, ' ', student_surname) AS student_name, student_dob, CONCAT(user_gname, ' ', user_surname) AS parent_name, 
			user_email AS parent_email, user_id AS parent_address, user_group AS parent_mobile, class_name, class_name, class_weekday, class_start_time, location_name, CONCAT(location_street, ' ', location_suburb, ' ', location_state, ' ', location_postcode) AS location_address,
			term_name, term_start_date, term_end_date, 
			user_hearabout as referral_source
		FROM tbl_student
		LEFT JOIN tbl_user ON user_id = student_user_id
		LEFT JOIN tbl_enrolment ON enrolment_student_id = student_id
		LEFT JOIN tbl_term ON term_object_id = enrolment_term_object_id
		LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
		LEFT JOIN tbl_location ON location_object_id = class_location_id 
		WHERE class_published = 1 AND term_published = 1 
  	AND enrolment_deleted IS NULL AND enrolment_published = 1 AND enrolment_type = 'T'
		{$whereloc}
		GROUP BY student_id ORDER BY student_name, class_deleted DESC, class_modified DESC, class_name
		";
if($res = $DBobject->wrappedSql($sql, $params)){
	foreach($res as &$r){
		$r['trial_date'] = getDateByWeekdayStartDate($r['class_weekday'],$r['term_start_date'], $r['trial_date'] - 1, 'd/m/Y');
		$userId = $r['parent_address'];
		$r['parent_address'] = getUserAddressInfo($userId, 'parent_address');
		$r['parent_mobile'] = getUserAddressInfo($userId, 'parent_mobile');
	}
	$filename='admin/includes/cron/reports/'.'TRIAL_CLASS_'.date('dmY').'.csv';
	$attFile =  $_SERVER['DOCUMENT_ROOT'].$filename;
	$buf = AssociativeArrayToCSV($res);
	if(file_put_contents($attFile, $buf)) $attachmentFile[] = $attFile;
}

//Select all locations which belong to this franchisee
$sql2 = "SELECT student_id, CONCAT(student_name, ' ', student_surname) AS student_name, student_dob, CONCAT(user_gname, ' ', user_surname) AS parent_name,
user_email AS parent_email, user_id AS parent_address, user_group AS parent_mobile, class_name, class_name, class_start_time, location_name, CONCAT(location_street, ' ', location_suburb, ' ', location_state, ' ', location_postcode) AS location_address,
term_name, term_start_date, term_end_date,
user_hearabout as referral_source
FROM tbl_student
LEFT JOIN tbl_user ON user_id = student_user_id
LEFT JOIN tbl_enrolment ON enrolment_student_id = student_id
LEFT JOIN tbl_term ON term_object_id = enrolment_term_object_id
LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
LEFT JOIN tbl_location ON location_object_id = class_location_id
WHERE class_deleted IS NULL AND class_published = '1' AND term_published = '1' AND term_deleted IS NULL
AND enrolment_deleted IS NULL AND enrolment_published = '1' AND (enrolment_type = 'MV' OR enrolment_type = 'S')
{$whereloc}
GROUP BY student_id ORDER BY student_name, class_deleted DESC, class_modified DESC, class_name
";
if($res2 = $DBobject->wrappedSql($sql2, $params)){
	foreach($res2 as &$r){
		$userId = $r['parent_address'];
		$r['parent_address'] = getUserAddressInfo($userId, 'parent_address');
		$r['parent_mobile'] = getUserAddressInfo($userId, 'parent_mobile');
	}
	$filename='admin/includes/cron/reports/'.'CLASS_ENROLLED_PAID_'.date('dmY').'.csv';
	$attFile =  $_SERVER['DOCUMENT_ROOT'].$filename;
	$buf2 = AssociativeArrayToCSV($res2);
	if(file_put_contents($attFile, $buf2)) $attachmentFile[] = $attFile;
}

//Select all locations which belong to this franchisee
$sql3 = "SELECT student_id, CONCAT(student_name, ' ', student_surname) AS student_name, student_dob, CONCAT(user_gname, ' ', user_surname) AS parent_name,
user_email AS parent_email, user_id AS parent_address, user_group AS parent_mobile, class_name, class_name, class_start_time, location_name, CONCAT(location_street, ' ', location_suburb, ' ', location_state, ' ', location_postcode) AS location_address,
term_name, term_start_date, term_end_date,
user_hearabout as referral_source
FROM tbl_student
LEFT JOIN tbl_user ON user_id = student_user_id
LEFT JOIN tbl_enrolment ON enrolment_student_id = student_id
LEFT JOIN tbl_term ON term_object_id = enrolment_term_object_id
LEFT JOIN tbl_class ON class_object_id = enrolment_class_object_id
LEFT JOIN tbl_location ON location_object_id = class_location_id
WHERE class_published = '1' AND term_published = '1' 
AND enrolment_deleted IS NULL AND enrolment_published = '1' AND (enrolment_type = 'MV' OR enrolment_type = 'S')
{$whereloc}
GROUP BY student_id ORDER BY student_name, class_deleted DESC, class_modified DESC, class_name
";
if($res3 = $DBobject->wrappedSql($sql3, $params)){
	foreach($res3 as &$r){
		$userId = $r['parent_address'];
		$r['parent_address'] = getUserAddressInfo($userId, 'parent_address');
		$r['parent_mobile'] = getUserAddressInfo($userId, 'parent_mobile');
	}
	$filename='admin/includes/cron/reports/'.'CLASS_ALL_ENROLLED_PAID_'.date('dmY').'.csv';
	$attFile =  $_SERVER['DOCUMENT_ROOT'].$filename;
	$buf3 = AssociativeArrayToCSV($res3);
	if(file_put_contents($attFile, $buf3)) $attachmentFile[] = $attFile;
} 

//Send email
if(!empty($attachmentFile)){
	$to = "briony@drivenbusiness.com.au";
	$subject = "Franchise Fortnightly Report";
	$from = (string) $CONFIG->company->name;
	$fromEmail = 'noreply@'. str_replace('www.', '', $GLOBALS['HTTP_HOST']);
	$bcc = "readysteadygokids@gmail.com-dev";
	$COMP = json_encode($CONFIG->company);
	$SMARTY->assign('COMPANY', json_decode($COMP,TRUE));
	$SMARTY->assign('content', 'See attached files.<br>');
	$SMARTY->assign('DOMAIN', "http://" . $GLOBALS['HTTP_HOST']);
	$body = $SMARTY->fetch('email-custom.tpl');
	sendAttachMail($to, $from, $fromEmail, $subject, $body, $bcc, $attachmentFile);
	die('=> Sent!');
}

//sendMail($devAdmin, $from, $fromEmail, "CRON JOB - PARTY $day", json_encode($events));
echo "<br>=> Cron ended";


function getUserAddressInfo($_userId, $_field){
	global  $DBobject,$USRADDR;
	
	if(empty($USRADDR[$_userId])){
		$sql = "SELECT CONCAT(address_line1, ' ', address_suburb, ' ', address_state, ' ', address_postcode) AS parent_address, address_telephone AS parent_mobile 
				FROM tbl_address WHERE address_user_id = :id ORDER BY address_modified DESC";
		if($res = $DBobject->wrappedSql($sql,array(":id"=>$_userId))){
			$USRADDR[$_userId] = $res[0];
		}
	}
	return $USRADDR[$_userId][$_field];
}
